exec Gp_StockBatch '1','',0,0,'','','','','','','','0','',0,2,'0$$','',default,default
ALTER PROCEDURE [dbo].[Gp_StockBatch]
    @Krec varchar(1000)='', --仓库
        @ptypeid varchar(50)='', --商品
        @CsRec int=0,--生产厂商
        @Brec int=0, --供应商
        @PrecName varchar(50)='', --商品名称过滤
        @CsName varchar(50)='', --生产厂商过滤
        @FL varchar(2000)='',--所属分类
        @rkDate varchar(50)='',
        @rkDate1 varchar(50)='',
        @yhDate varchar(50)='',
        @yhDate1 varchar(50)='',
        @FilterStr varchar(50)='',
        @FilterValue varchar(50)='',
        @Posrec int=0,
        @LoginUserRec int=0,
        @sx varchar(100)='' ,
        @FilterValue2 VARCHAR(200)='',
        @ORec INT=0,
        @ClosePFLY int=0
    AS
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    declare
        @sql varchar(7000), @sql1 varchar(1000), @KtypeId varchar(50), @sql2 varchar(500)
    declare
        @btypeid cttypeid, @IFOwnertypeRight varchar(50)
    set @sql2 = ''
    if @ClosePFLY = 1
        set @sql2 = ' into #temp '
    if @Brec > 0
        select @btypeid = typeid from btype where Rec = @Brec
    else
        set @btypeid = ''
    declare
        @SelfBtID varchar(50), @sTypeid varchar(50)
    set @SelfBtID = ''
    SET @sTypeid = ''
    declare
        @ZDYcol int --自定义列号
    if @sx <> '0$$'
        begin
            Select @SelfBtID = Substring(@sx, 0, CHARINDEX('$$', @sx))
            Select @sTypeid = Substring(@sx, CHARINDEX('$$', @sx) + 2, Len(@sx))

            select @ZDYcol = BasicCol from Xw_SelfBT where SelfBtID = @SelfBtID and ClassID = 1
            --set @sql = @sql + ' and C'+cast(@ZDYcol as varchar)+'='+''''+@sTypeid+''''
        end
    SELECT rec
    INTO #tempCrec
    FROM xw_selfdata a
             INNER JOIN dbo.GetStrListTable(@sTypeid) b ON a.sTypeid = b.sTypeid

    --select * into #stock from dbo.fn_KTypeRight(@LoginUserRec)
    --select * into #btype from dbo.fn_BTypeRight(@LoginUserRec)
    --SELECT * INTO #ptype FROM dbo.fn_PTypeRight(@LoginUserRec)
    declare
        @stocktable varchar(100)
    declare
        @ptypetable varchar(100)
    declare
        @btypetable varchar(100)
    if @LoginUserRec <= 1 or exists(select *
                                    from sysdata (nolock)
                                    where subname = 'Stockright'
                                      and subvalue = '0')
        set @stocktable = 'stock'
    else
        set @stocktable = 'dbo.fn_KTypeRight(' + cast(@LoginUserRec as varchar(20)) + ')'
    if @LoginUserRec <= 1 or exists(select *
                                    from sysdata (nolock)
                                    where subname = 'BtypeRight'
                                      and subvalue = '0')
        set @btypetable = 'btype'
    else
        set @btypetable = 'dbo.fn_BTypeRight(' + cast(@LoginUserRec as varchar(20)) + ')'
    if @LoginUserRec <= 1 or exists(select *
                                    from sysdata (nolock)
                                    where subname = 'PtypeRight'
                                      and subvalue = '0')
        set @ptypetable = 'ptype'
    else
        set @ptypetable = 'dbo.fn_PTypeRight(' + cast(@LoginUserRec as varchar(20)) + ')'
    select *
    into #aa
    from (select typeid, 1 as ConnectType
          from stock
          where rec in (select krec from posinfo where Deleted = 0 and connecttype = 1)
          union
          select 'C' + typeid, 1 as ConnectType
          from stock
          where rec in (select krec from posinfo where Deleted = 0 and connecttype = 1)
          union
          select '99999', 1
         ) aa
    set @sql1 = '  '
    select @sql = 'select top 500000
				(select sum(qty) from zxy_goodsstock_guoyao where goodsId = (select rec from ptype where typeId = p.typeid)
				and lotno = a.Jobnumber) as guoyao_stock,
			  cast(GJobID as  varchar(50))GJobID, p.typeid ptypeid, s.typeid ktypeid, k.typeid gtypeid,datediff(day,intime,GETDATE()) as days,intime,a.ORec,
                   case a.Type when ''C'' then (case when p.TinyRate>0 then a.Qty/p.TinyRate else a.Qty end)
                   else a.Qty end as Qty,
                   a.ProviderId as gysId,cast(a.StockUniqueid as varchar(50)) as StockUniqueid,
                   a.PtypeSource,a.StopUse,
                   case a.Type when ''C'' then (case when p.TinyRate>0 then a.Price*p.TinyRate else a.Price end)
                   else a.Price end as Price,
                   a.Price AS operCostPrice,
                   a.price*a.qty as total, b.UserCode as gyscode,b.FullName as gysName,a.GoodsId,
                   a.Jobnumber,a.OutFactoryDate,
                   case a.ValidityPeriod when null then '''' else
                   DATEDIFF(day,GETDATE(),a.ValidityPeriod) end limitday,
                   a.ValidityPeriod,a.JobCode,a.GermJobNumber,a.CheckReportNo,--cast(a.Gjobid as varchar(50)) gjobid,
                   case a.PtypeSource when 0 then ''正常采购'' when 1 then ''代销''
                   when 2 then ''赠送'' else ''正常采购'' end psource,
                   case a.StopUse when 0 then ''正常'' when 1 then ''停售'' when 2 then ''暂停销售''
                   when 4 then ''销毁'' else ''正常'' end pstate,bi.SalePrice as zjprice,
                   isnull(p2.ConnectType,0) as kconnectType,a.JobTax,(case a.Type when ''C'' then (case when p.TinyRate>0 then a.Price*p.TinyRate else a.Price end)
                   else a.Price end ) * (100+a.JobTax)/100 as JObTaxPrice '
        + @sql2 +
                  ' from vGoodsStocks a
                    inner join ' + @ptypetable + ' p on a.PRec = p.rec
                 inner join ' + @btypetable + ' b on b.Rec = a.ProviderId
                 inner join ' + @stocktable + ' s on s.Rec = a.KRec
                 join klocation k on k.Rec = a.GRec
                 --left join Posinfo p2 on a.KRec=p2.krec and p2.Deleted=0
                 left join #aa p2 on p2.typeid = s.typeid
                 left join (select
                                   dj.PRec,x2.DiscountPrice as SalePrice
					          from (select PRec,max(buyid)as buyid
					                  from vBuyBill b1 join vbillindex b2 on b1.BillID = b2.BillID
					                 where b2.BillType = 34 and b2.RedWord=0
					                 group by PRec) dj
				            	join vBuyBill x2 on  dj.buyid=x2.buyid ) bi on a.PRec=bi.PRec
                 inner join cstype cs on cs.Rec = p.Area '
        + ' where  p.deleted=0   and (a.Type <> ''T'' and a.Type <> ''D'')  '
    if @Krec <> ''
        begin
            if charindex(',', @Krec, 0) = 0
                select @sql = @sql + ' and a.krec=' + @Krec
            else
                select @sql = @sql + ' and a.krec in(' + @Krec + ')'
        end
    --inner join dbo.GetBillTypeTable(''' + @Krec + ''') t on a.KRec = t.ObjectID or t.ObjectID = 0
    if @CsRec <> 0
        select @sql = @sql + ' and p.Area =' + cast(@CsRec as varchar(10))
    --+ ' and (a.ProviderId ='+cast(@Brec as varchar(10))+ ' or 0 ='+cast(@Brec as varchar(10))+') '
    --+ ' and (p.Rec in (Select PRec from PtypeWorkRange pr where pr.PtypeClassID='+CAST(@FL AS VARCHAR)+') or ('+CAST(@FL AS VARCHAR)+'=0)  ) '
    if @FL <> ''
        select @sql = @sql + ' and ((''' + CAST(@FL AS VARCHAR) +
                      '''='''') or EXISTS(select 1 from PtypeWorkRange pr where p.rec = pr.PRec AND  pr.PtypeClassID in (Select * from dbo.GetBillTypeTable(''' +
                      CAST(@FL AS VARCHAR) + '''))))	'
    if @ptypeid <> ''
        select @sql = @sql + ' and ((p.typeId like ''' + @ptypeid + '%''' + ')or(''' + @ptypeid + '''=''''))  '
    if @btypeid <> ''
        select @sql = @sql + ' and ((b.typeId like ''' + @btypeid + '%''' + ')or(''' + @btypeid + '''=''''))  '
    if @sx <> '0$$'
        select @sql = @sql + ' and ((''' + @sx + ''' = ''0$$'') OR (''' + @sx +
                      ''' = '''') OR EXISTS(SELECT 1 FROM ptypecustom pc WHERE pc.Prec=p.Rec and pc.Crec in (select rec from #tempCrec)  )) '
    if @PrecName <> ''
        begin
            set @PrecName = '%' + isnull(@PrecName, '') + '%'
            set @sql = @sql + ' and isnull(p.FullName,'''') like ''' + @PrecName + ''''
        end
    if @CsName <> ''
        begin
            set @CsName = '%' + isnull(@CsName, '') + '%'
            set @sql = @sql + ' and isnull(cs.FullName,'''') like ''' + @CsName + ''''
        end
    select @IFOwnertypeRight = subvalue
    from sysdata
    where subname = 'OwnertypeRight'
    if @IFOwnertypeRight = '1'
        set @sql = @sql + 'and EXISTS(SELECT 1 FROM dbo.fn_OwnerTypeRight(' + CAST(@LoginUserRec AS varchar) +
                   ') o WHERE o.Rec = a.orec) '
    if @OREC <> 0
        SET @sql = @sql + ' and a.OREC = ' + CAST(@OREC AS varchar) + ' '
    if @FilterStr = '1'
        begin
            set @sql1 = @sql1 + ' and a.ValidityPeriod < ' + char(39) + @FilterValue + char(39)
        end
    if @FilterStr = '2'
        begin
            set @sql = @sql + ' and a.ValidityPeriod <= ' + char(39) + @FilterValue + char(39)
        end
    if @FilterStr = '3'
        begin
            set @sql1 = @sql1 +
                        ' and (datediff(day,convert(varchar(10),getdate(),20),a.ValidityPeriod) <= p.ValidityDays  and datediff(day,convert(varchar(10),getdate(),20),a.ValidityPeriod) >= 0) ' -- 只查询效期，不查询过期的商品  >0
        end
    if @FilterStr = '4'
        set @sql1 = @sql1 + ' and a.ValidityPeriod < convert(varchar(10),getdate(),20) '
    if @FilterStr = '5'
        set @sql1 = @sql1 + ' and a.ValidityPeriod is null '
    if @rkDate <> ''
        begin
            set @sql1 = @sql1 + ' and a.intime >= ' + char(39) + @rkDate + char(39) + ' and a.intime <= ' + char(39) +
                        @rkDate1 + char(39)
        end
    if @yhDate <> ''
        begin
            set @sql1 = @sql1 + ' and a.lastmaintainTime >= ' + char(39) + @yhDate + char(39) +
                        ' and a.lastmaintainTime <= ' + char(39) + @yhDate1 + char(39)
        end
    if @Posrec > 0
        begin
            select @KtypeId = typeId from Stock where rec = (select KRec from PosInfo where PosId = @Posrec)
            set @KtypeId = isnull(@KtypeId, '') + '%'
            set @sql = @sql + ' and (s.typeId like ''' + @KtypeId + ''' or s.typeid like ''C' + @KtypeId +
                       ''' or s.typeid =''99999'' ) '
        end
    IF @FilterValue2 <> ''
        SET @sql = @sql + 'and ( a.ValidityPeriod is not null and DATEDIFF(day,GETDATE(),a.ValidityPeriod) between ' +
                   @FilterValue2 + ')'

    --set @sql = @sql + ' ORDER BY p.typeid '

    set @sql = @sql + @sql1 + ' ORDER BY p.rec '
    if @ClosePFLY = 1
        SET @sql = @sql
            + '   select t.ptypeid,t.ktypeid,t.gtypeid,t.days,t.intime,SUM(ORec) ORec,SUM(Qty) Qty,t.gysId,t.PtypeSource,
                            t.StopUse,t.Price,t.operCostPrice,SUM(total) total,t.gyscode,t.gysName,t.JobNumber,t.OutFactoryDate,
                            t.limitday,t.ValidityPeriod,t.JobCode,t.GermJobNumber,t.CheckReportNo,t.psource,t.pstate,t.zjprice,
                            t.JObTax,t.JObTaxPrice
                       from #temp t
                   group by t.ptypeid,t.ktypeid,t.gtypeid,t.days,t.intime,t.gysId,t.PtypeSource,t.StopUse,t.Price,
                            t.operCostPrice,t.gyscode,t.gysName,t.JobNumber,t.OutFactoryDate,t.limitday,t.ValidityPeriod,
                            t.JobCode,t.GermJobNumber,t.CheckReportNo,t.psource,t.pstate,t.zjprice,t.JObTax,t.JObTaxPrice'
    print (@sql)
    exec (@sql)
